"""
Author: Eddy
Data: 2022/3/1

使用前请确认cookie的有效性
"""

import datetime
import os
import re
import traceback
from threading import Thread
from auto_driver import AutoDriver
from auto_get_mouse_position import mouse_move
from sqlalchemy.types import NVARCHAR, Float, Integer
from selenium.webdriver.common.by import By
from cookie import *
import time
import pandas as pd
import numpy as np
from sqlalchemy import create_engine

ILLEGAL_CHARACTERS_RE = re.compile(r'[\000-\010]|[\013-\014]|[\016-\037]')


def key_board_interrupt_ignore(func):
    def run(*args, **kwargs):
        try:
            start_time = datetime.datetime.now()
            func(*args, **kwargs)
            end_time = datetime.datetime.now()
            print(f"\nEnd!\n耗时:{(end_time - start_time).seconds} 秒")
        except KeyboardInterrupt:
            print("手动结束!")
        except ConnectionError:
            print("ConnectionError: 请求错误！")
        except Exception as e:
            print(traceback.print_exc())
            print(e)
    return run


def login_in():
    """
    登陆后台
    :return:
    """
    url = "https://dy.feigua.cn/Member#/LiveOverview"
    obj = AutoDriver(url, cookie=cookie)
    obj.set_cookie()

    driver = obj.driver
    driver.find_element(by=By.XPATH, value="/html/body/div[5]/div[1]/a").click()
    try:
        login_title = driver.find_element(by=By.XPATH, value="/html/body/div[1]/div[1]/div/div[2]/div/span/span").text
        print("登陆成功:", login_title)
    except Exception as e:
        print("错误...未成功登陆", e)

    driver.find_element(by=By.LINK_TEXT, value="进入").click()
    try:
        driver.find_element(by=By.LINK_TEXT, value="确定").click()
    except Exception as e:
        print("没有找到确认按钮\n", e)
    return driver


def save_excel_column(wb, value_lists, save_path, sheet_no=0):
    sheet = wb[wb.sheetnames[sheet_no]]
    for valueList in value_lists:
        col = sheet.max_column + 1
        for i in range(len(valueList)):
            value = ILLEGAL_CHARACTERS_RE.sub(r'', str(valueList[i]))
            sheet.cell(i + 1, col, value)
    print("Saving...", save_path)
    wb.save(save_path)


def sign_env():
    driver = login_in()
    # 设置爬取环境 -> 进入数据大盘, 设置时间段为近90天
    xpath_list = [
        '//*[@id="__ulNav"]/li[2]/a',
        '//*[@id="__ulNav"]/li[2]/div/ul/li[4]/a',
        "/html/body/div[3]/div/div/div/div[9]/div[1]/div[2]/div[1]/button[4]",
        "/html/body/div[3]/div/div/div/div[10]/div[1]/div[2]/div[1]/button[2]"
    ]
    for xpath in xpath_list:
        element = driver.find_element(by=By.XPATH, value=xpath)
        driver.execute_script('arguments[0].scrollIntoView();', element)
        element.click()
        time.sleep(1)
    return driver


def mapping_df_types(df):
    """
    判断DataFrame各字段类型, 保证正确存入数据库
    :param df:
    :return:
    """
    type_dict = {}
    for i, j in zip(df.columns, df.dtypes):
        if "object" in str(j):
            type_dict[i] = NVARCHAR(length=255)
        if "float" in str(j):
            type_dict[i] = Float(precision=2, asdecimal=True)
        if "int" in str(j):
            type_dict[i] = Integer()
    return type_dict


def run_mouse():
    start = {"x": 338, "y": 397}
    end = {"x": 1847, "y": 397}
    mouse_move(start, end, 15)


def analytical_data(driver):  # sourcery no-metrics
    """
    配合鼠标移动，动态获取数据
    :param driver:
    :return:
    """

    data_configs = [
        {
            "data_sheet": "/html/body/div[3]/div/div/div/div[9]/div[3]/div/div[1]",
            "data_value": "/html/body/div[3]/div/div/div/div[9]/div[3]/div/div[2]",
            "data_title": "直播带货场次趋势",
            "data_columns": ["日期", "实时观众数", "带货直播间数", "上架商品数", "类目"],
            "sql_table_name": "liveBroadcastTrend"
        },
        {
            "data_sheet": "/html/body/div[3]/div/div/div/div[10]/div[3]/div/div[1]",
            "data_value": "/html/body/div[3]/div/div/div/div[10]/div[3]/div/div[2]",
            "data_title": "直播带货GMV趋势",
            "data_columns": ["日期", "预估销售额", "预估销量", "分钟销量产出", "分钟销售额产出", "类目"],
            "sql_table_name": "liveGMVTrend"
        }
    ]

    menu_option = "/html/body/div[3]/div/div/div/div[8]/div/div[1]/div[1]/div"

    for data_config in data_configs:
        data_list = []
        for i in range(1, 20):
            # 类目选择
            select_options = f"/html/body/div[3]/div/div/div/div[8]/div/div[1]/div[2]/ul[1]/li[{i}]"
            # 类目菜单
            e = driver.find_element(By.XPATH, menu_option)
            driver.execute_script('arguments[0].scrollIntoView();', e)
            e.click()
            time.sleep(1)

            # 子菜单
            e = driver.find_element(By.XPATH, select_options)
            driver.execute_script('arguments[0].scrollIntoView();', e)
            e.click()
            time.sleep(1)

            # 类目title
            sale_class = e.text

            # 收起子菜单
            e = driver.find_element(By.XPATH, "/html/body/div[3]/div/div/div/div[9]/div[1]/div[2]/div[1]/button[4]")
            driver.execute_script('arguments[0].scrollIntoView();', e)
            e.click()

            element = driver.find_element(By.XPATH, data_config["data_sheet"])
            driver.execute_script('arguments[0].scrollIntoView();', element)  # 移动图表到屏幕中间
            time.sleep(3)

            # 增加线程，控制鼠标移动，动态加载数据
            run_mouse_thread = Thread(target=run_mouse)
            run_mouse_thread.start()

            # 加锁, 判断数据是否停止更新, 判断是否为重复数据
            lock = 0
            last_lock_str = ""

            # 无限加载数据, 突破锁限制结束循环
            while True:
                try:
                    value = driver.find_element(By.XPATH, data_config["data_value"]).text
                    if lock > 500:
                        break
                    if len(value) == 0:
                        lock += 1
                        continue

                    value_list = value.split("\n")

                    value_list.append(sale_class)  # 追加商品类型列  todo:需要追加数据 -> 完成
                    n = len(value_list)

                    # 判断数据列数，添加对应个数的列表
                    if not data_list:
                        data_list.extend([] for _ in range(n))
                    # 判断是否为重复值
                    lock_str = "".join(value_list)
                    if lock_str == last_lock_str:
                        continue

                    # 数据追加到列表里
                    for index in range(n):
                        value = value_list[index]
                        if ":" in value:
                            # 数据处理, 去文本前缀, 去千分位符, 转为整形
                            value = int(value.split(":")[1].strip().replace(",", ""))
                        data_list[index].append(value)
                    last_lock_str = lock_str

                    print("sale_class:", sale_class)  # todo: 打印明细
                    print("value_list:", value_list)
                except Exception as e:
                    print("循环获取数据中的Exception:\n", e)
        print("saving... ", data_config["data_title"])

        # 保存方式1: 列表数据保存至文件内
        # file_name = data["data_title"] + ".xlsx"
        # work_book = Workbook()
        # work_book.create_sheet()
        # save_excel_column(work_book, data_list, file_name)

        # 保存方式2: 转换为DataFrame,存储至文件或者数据库
        arr = np.array(data_list)
        data_frame = pd.DataFrame(arr.T, columns=data_config["data_columns"])  # 需要行列转置,指定列名称
        data_frame.to_csv(data_config["data_title"] + ".csv", index=False, encoding="utf-8-sig")

        # 存入数据库 todo: 需要进行去重操作, 且没有PrimaryKey
        # todo: 可以新增sql操作类, 封装执行sql方法, 用于常规操作
        engin = create_engine("mysql+pymysql://root:123123@localhost:3306/sample", echo=False)
        data_frame.to_sql(
            name=data_config["sql_table_name"],
            con=engin,
            index=False,
            chunksize=5000,
            if_exists="append",
            dtype=mapping_df_types(data_frame)
        )


@key_board_interrupt_ignore
def main():
    driver = sign_env()
    analytical_data(driver)


class XlsxClear:
    """
    数据清洗, 去千分位符, 格式化
    """

    def __init__(self, file_path="./"):
        file_list = self.listdir(file_path)
        for file in file_list:
            print("reading..", file)
            df = pd.read_excel(file)
            df.to_csv(os.path.splitext(file)[0] + ".csv", encoding="utf-8-sig", index=False)

    def listdir(self, path, file_dir=True):  # 传入根目录
        file_list = []
        for file in os.listdir(path):
            file_path = os.path.join(path, file)  # 获取绝对路径
            if os.path.isdir(file_path):  # 如果还是文件夹，就继续迭代本函数
                if file_dir:
                    self.listdir(file_path)
            elif os.path.splitext(file_path)[1] in ['.xls', '.xlsx']:  # 判断文件是否是Excel文件
                file_list.append(file_path)
        return file_list


if __name__ == '__main__':
    main()
    XlsxClear()
